import pymssql

# 数据库连接配置
server = 'localhost'  # 例如 'localhost' 或 '192.168.1.1'
database = 'DB课设'  # 数据库名
username = 'sa'  # 用户名
password = '123456'  # 密码

# 连接字符串
conn_str = f'server={server};database={database};user id={username};password={password}'

# 实体类
class Academy:
    def __init__(self, academy_id, academy_name):
        self.academy_id = academy_id
        self.academy_name = academy_name

class SubjectInfo:
    def __init__(self, subject_id, academy_id, subject_name, subject_type, subject_level, parent_subject, subject_description):
        self.subject_id = subject_id
        self.academy_id = academy_id
        self.subject_name = subject_name
        self.subject_type = subject_type
        self.subject_level = subject_level
        self.parent_subject = parent_subject
        self.subject_description = subject_description

class SubjectExam:
    def __init__(self, subject_id, subject_one_id, subject_two_id, subject_three_id, subject_four_id):
        self.subject_id = subject_id
        self.subject_one_id = subject_one_id
        self.subject_two_id = subject_two_id
        self.subject_three_id = subject_three_id
        self.subject_four_id = subject_four_id

class AdmissionPlan:
    def __init__(self, academy_id, subject_id, total_quota, additional_quota, year, tutor_total):
        self.academy_id = academy_id
        self.subject_id = subject_id
        self.total_quota = total_quota
        self.additional_quota = additional_quota
        self.year = year
        self.tutor_total = tutor_total

class TutorInfo:
    def __init__(self, tutor_id, tutor_name, tutor_title, subject1, subject2, subject3, tutor_phone, tutor_email, tutor_profile, tutor_image):
        self.tutor_id = tutor_id
        self.tutor_name = tutor_name
        self.tutor_title = tutor_title
        self.subject1 = subject1
        self.subject2 = subject2
        self.subject3 = subject3
        self.tutor_phone = tutor_phone
        self.tutor_email = tutor_email
        self.tutor_profile = tutor_profile
        self.tutor_image = tutor_image

class Tutor:
    def __init__(self, tutor_id, academy_id, subject_id, tutor_role, tutor_quota):
        self.tutor_id = tutor_id
        self.academy_id = academy_id
        self.subject_id = subject_id
        self.tutor_role = tutor_role
        self.tutor_quota = tutor_quota

class ResearchDirection:
    def __init__(self, id, name, subject_id):
        self.id = id
        self.name = name
        self.subject_id = subject_id

# 接口
class DatabaseOperations:
    def connect(self):
        raise NotImplementedError

    def disconnect(self):
        raise NotImplementedError

    def insert_academy(self, academy):
        raise NotImplementedError

    def delete_academy(self, academy_id):
        raise NotImplementedError

    def update_academy(self, academy):
        raise NotImplementedError

    def insert_subject_info(self, subject_info):
        raise NotImplementedError

    def delete_subject_info(self, subject_id):
        raise NotImplementedError

    def update_subject_info(self, subject_info):
        raise NotImplementedError

    def insert_subject_exam(self, subject_exam):
        raise NotImplementedError

    def delete_subject_exam(self, subject_id):
        raise NotImplementedError

    def update_subject_exam(self, subject_exam):
        raise NotImplementedError

    def insert_admission_plan(self, admission_plan):
        raise NotImplementedError

    def delete_admission_plan(self, academy_id, subject_id):
        raise NotImplementedError

    def update_admission_plan(self, admission_plan):
        raise NotImplementedError

    def insert_tutor_info(self, tutor_info):
        raise NotImplementedError

    def delete_tutor_info(self, tutor_id):
        raise NotImplementedError

    def update_tutor_info(self, tutor_info):
        raise NotImplementedError

    def insert_tutor(self, tutor):
        raise NotImplementedError

    def delete_tutor(self, tutor_id, subject_id):
        raise NotImplementedError

    def update_tutor(self, tutor):
        raise NotImplementedError

    def insert_research_direction(self, research_direction):
        raise NotImplementedError

    def delete_research_direction(self, id, subject_id):
        raise NotImplementedError

    def update_research_direction(self, research_direction):
        raise NotImplementedError

# 实现类
class SQLServerDatabaseOperations(DatabaseOperations):
    def __init__(self):
        self.conn = None

    def connect(self):
        try:
            self.conn = pymssql.connect(server=server, user=username, password=password, database=database)
            self.cursor = self.conn.cursor()
        except Exception as e:
            print(f"Error connecting to SQL Server: {e}")

    def disconnect(self):
        if self.conn:
            self.conn.close()

    def insert_academy(self, academy):
        sql = "INSERT INTO 学院表 (学院编号, 学院名称) VALUES (%s, %s)"
        self.cursor.execute(sql, (academy.academy_id, academy.academy_name))
        self.conn.commit()

    def delete_academy(self, academy_id):
        sql = "DELETE FROM 学院表 WHERE 学院编号 = %s"
        self.cursor.execute(sql, (academy_id,))
        self.conn.commit()

    def update_academy(self, academy):
        sql = "UPDATE 学院表 SET 学院名称 = %s WHERE 学院编号 = %s"
        self.cursor.execute(sql, (academy.academy_name, academy.academy_id))
        self.conn.commit()

    def insert_subject_info(self, subject_info):
        sql = """
        INSERT INTO 学科信息表 (学科编号, 学院编号, 学科名称, 学科类型, 学科等级, 上级学科, 学科概述)
        VALUES (%s, %s, %s, %s, %s, %s, %s)
        """
        self.cursor.execute(sql, (subject_info.subject_id, subject_info.academy_id, subject_info.subject_name,
                                 subject_info.subject_type, subject_info.subject_level, subject_info.parent_subject,
                                 subject_info.subject_description))
        self.conn.commit()

    def delete_subject_info(self, subject_id):
        sql = "DELETE FROM 学科信息表 WHERE 学科编号 = %s"
        self.cursor.execute(sql, (subject_id,))
        self.conn.commit()

    def update_subject_info(self, subject_info):
        sql = """
        UPDATE 学科信息表
        SET 学科名称 = %s, 学科类型 = %s, 学科等级 = %s, 上级学科 = %s, 学科概述 = %s
        WHERE 学科编号 = %s
        """
        self.cursor.execute(sql, (subject_info.subject_name, subject_info.subject_type, subject_info.subject_level,
                                 subject_info.parent_subject, subject_info.subject_description, subject_info.subject_id))
        self.conn.commit()

    def insert_subject_exam(self, subject_exam):
        sql = """
        INSERT INTO 学科考试科目表 (学科编号, 科目一编号, 科目二编号, 科目三编号, 科目四编号)
        VALUES (%s, %s, %s, %s, %s)
        """
        self.cursor.execute(sql, (subject_exam.subject_id, subject_exam.subject_one_id, subject_exam.subject_two_id,
                                 subject_exam.subject_three_id, subject_exam.subject_four_id))
        self.conn.commit()

    def delete_subject_exam(self, subject_id):
        sql = "DELETE FROM 学科考试科目表 WHERE 学科编号 = %s"
        self.cursor.execute(sql, (subject_id,))
        self.conn.commit()

    def update_subject_exam(self, subject_exam):
        sql = """
        UPDATE 学科考试科目表
        SET 科目一编号 = %s, 科目二编号 = %s, 科目三编号 = %s, 科目四编号 = %s
        WHERE 学科编号 = %s
        """
        self.cursor.execute(sql, (subject_exam.subject_one_id, subject_exam.subject_two_id, subject_exam.subject_three_id,
                                 subject_exam.subject_four_id, subject_exam.subject_id))
        self.conn.commit()

    def insert_admission_plan(self, admission_plan):
        sql = """
        INSERT INTO 招生计划表 (学院编号, 学科编号, 学科年度总指标, 学科年度补充指标, 年度, 学科导师总数)
        VALUES (%s, %s, %s, %s, %s, %s)
        """
        self.cursor.execute(sql, (admission_plan.academy_id, admission_plan.subject_id, admission_plan.total_quota,
                                 admission_plan.additional_quota, admission_plan.year, admission_plan.tutor_total))
        self.conn.commit()

    def delete_admission_plan(self, academy_id, subject_id):
        sql = "DELETE FROM 招生计划表 WHERE 学院编号 = %s AND 学科编号 = %s"
        self.cursor.execute(sql, (academy_id, subject_id))
        self.conn.commit()

    def update_admission_plan(self, admission_plan):
        sql = """
        UPDATE 招生计划表
        SET 学科年度总指标 = %s, 学科年度补充指标 = %s, 学科导师总数 = %s
        WHERE 学院编号 = %s AND 学科编号 = %s
        """
        self.cursor.execute(sql, (admission_plan.total_quota, admission_plan.additional_quota, admission_plan.tutor_total,
        admission_plan.academy_id, admission_plan.subject_id))
        self.conn.commit()

def main():
# 创建数据库操作实例
    db_ops = SQLServerDatabaseOperations()

    # 连接到数据库
    db_ops.connect()

    try:
        # 创建学院实体
        new_academy = Academy('004', '信息学院')
        
        # 插入学院数据
        db_ops.insert_academy(new_academy)
        print("学院数据插入成功！")
        
    except Exception as e:
        print(f"插入学院数据时发生错误: {e}")

    finally:
        # 断开数据库连接
        db_ops.disconnect()

        
if __name__ == "__main__":
    main()
